package dao;

import java.math.BigDecimal;
import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

import on.*;

/**
 * Accesos BDD tabla operaciones
 * 
 * @author lgonzalez
 * @version 1.0 15-05-2013
 * @since 15-05-2013
 *
 */
public class DaoOp {

	private static Logger __log = LogManager.getLogger(DaoOp.class);
	
	public int suma(Connection con, Tarjeta miTar, double cant,
			String observacion) throws SQLException {
		int rows = 0;
		PreparedStatement miPs = null;
		String cadena = "insert into operaciones (idClie,idTar,suma,observacion,fecha) values(?,?,?,?,datetime('now'))";
		int cont = 1;
		try {
			miPs = con.prepareStatement(cadena);
			miPs.setInt(cont++, miTar.getIdCliente());
			miPs.setString(cont++, miTar.getIdTarjeta());
			miPs.setDouble(cont++, cant);
			miPs.setString(cont++, observacion);
			rows = miPs.executeUpdate();

		} finally {
			miPs.close();
		}
		return rows;
	}

	public int resta(Connection con, Tarjeta miTar, double cant,
			String observacion) throws SQLException {
		int rows = 1;

		PreparedStatement miPs = null;
		String cadena = "insert into operaciones (idClie,idTar,resta,observacion,fecha) values(?,?,?,?,datetime('now'))";
		int cont = 1;
		try {
			miPs = con.prepareStatement(cadena);
			miPs.setInt(cont++, miTar.getIdCliente());
			miPs.setString(cont++, miTar.getIdTarjeta());
			miPs.setDouble(cont++, cant);
			miPs.setString(cont++, observacion);

			rows = miPs.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			miPs.close();
		}

		return rows;
	}

	public double consSaldo(Connection con, Tarjeta miTar) throws SQLException {
		// int rows = 1;
		double saldo = 0.00;		
		PreparedStatement miPs = null;
		ResultSet rs;
		//String cadena = "select sumi-resti from(select ifnull(sum(suma),0) as sumi,ifnull(sum(resta),0) as resti from operaciones where idTar=?)";
		String cadena ="select sum(suma), sum(resta) from operaciones where idTar=?";
		// id cliente, id tarjeta, suma, resta y fecha

		try {
			miPs = con.prepareStatement(cadena);
			miPs.setString(1, miTar.getIdTarjeta());

			// saldo = miPs.executeQuery().getDouble(1);
			rs = miPs.executeQuery();
			while (rs.next()) {		
				BigDecimal sumas=BigDecimal.valueOf(rs.getDouble(1));
				BigDecimal restas=BigDecimal.valueOf(rs.getDouble(2));				
				BigDecimal resultado= sumas.subtract(restas);
				saldo =resultado.doubleValue();
				__log.info("resultado resta es:"+saldo);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			miPs.close();
		}

		return saldo;
	}

	public ArrayList<Operacion> consOp(Connection con, Tarjeta miTar,
			Cliente miCli, Date inicio, Date fin) throws SQLException,
			ParseException {

		Statement miSt = null;

		ResultSet rs;
		ArrayList<Operacion> list = new ArrayList<Operacion>();
		StringBuffer bufCad = new StringBuffer();

		String patronFecha = "yyyy-MM-dd HH:mm:ss";

		SimpleDateFormat miSDF = new SimpleDateFormat(patronFecha);

		bufCad.append("select * from operaciones where 1=1");
		if (miCli != null) {
			bufCad.append(" and idClie=");
			bufCad.append(miCli.getIdCliente());
		}
		if (miTar != null) {
			bufCad.append(" and idTar='");
			bufCad.append(miTar.getIdTarjeta());
			bufCad.append("'");
		}
		if ((inicio != null) & (fin != null)) {
			bufCad.append(" and fecha BETWEEN ");
			bufCad.append("'");
			bufCad.append(miSDF.format(inicio));
			bufCad.append("' and '");
			bufCad.append(miSDF.format(fin));
			bufCad.append("'");
		}

		try {
			miSt = con.createStatement();
			rs = miSt.executeQuery(bufCad.toString());

			while (rs.next()) {
				Operacion miOP = new Operacion();
				miOP.setIdCliente(rs.getInt("idClie"));
				miOP.setIdTarjeta(rs.getInt("idTar"));
				miOP.setSuma(rs.getDouble("suma"));
				miOP.setResta(rs.getDouble("resta"));
				miOP.setObservacion(rs.getString("observacion"));
				String strF= rs.getString("fecha");
				miOP.setFecha(miSDF.parse(strF));
				list.add(miOP);
			}

		} finally {
			miSt.close();
		}

		return list;
	}
	
	public int modificacionOperaciones(Tarjeta tar, Connection miCon, String NuevoID) throws SQLException {

		PreparedStatement miPs = null;
		
		int rows = 0;	
		String cadena= "update operaciones set idTar=? where idTar=? and idClie=?;";
		int count=1;
		
		try {
			miPs = miCon.prepareStatement(cadena);
			
			miPs.setString(count++,NuevoID);
			miPs.setString(count++,tar.getIdTarjeta());			
			miPs.setInt(count++, tar.getIdCliente());
			rows = miPs.executeUpdate();

		} finally {
			miPs.close();

		}
		return rows;

	}
	
}
